/**
 * 
 */
package com.danlu.admin.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import jxl.write.Label;
import jxl.write.WritableSheet;

/**
 * 导出excel的工具类
 * @author zx11
 *
 */
public class ExcelExportUtils {
	
	private static final Logger log = LoggerFactory.getLogger(ExcelExportUtils.class);
	
	private static final int EXCEL_SHEETSIZE = 50000; 
	
	private static final String EXPAND_NAME = ".xls";
	
	/**
	 * 生成excel文件并通过浏览器导出
	 * @param titleList 表格标题(不为空)
	 * @param headers  表格头，在标题之上
	 * @param footers  表格尾部内容（常用于统计信息）
	 * @param contentList 表格内容
	 * @param sheetCount  每页数据最大条数(默认为50000)
	 * @param response    HttpServletResponse
	 * @param fileName    excel文件名(带有扩展名)
	 */
	public static void exportExcel(List<String> titleList,List<String> headers,List<String> footers,List<List<String>> contentList,
			Integer sheetCount,HttpServletResponse response,String fileName) throws Exception{
		if (titleList == null || titleList.size() == 0 || contentList == null || response == null) {
			return;
		}
		try {
			WritableSheet wsheet = null;
			OutputStream os = response.getOutputStream();
			//清空response里的内容
			response.reset();
			ExcelCommon.getWritableWorkbook(os);
			int cellCount = titleList.size();   //单元格数
			int length = contentList.size();      //数据条数
			int sheetSize = (sheetCount == null ? EXCEL_SHEETSIZE : sheetCount.intValue());
			if (sheetSize == 0) {
				sheetSize = EXCEL_SHEETSIZE;
			}
			int sheetNum = 0;  //sheet页数
			if (length % sheetSize > 0) {
				sheetNum = length / sheetSize + 1;
			} else {
				sheetNum = length / sheetSize;
			}
			if (sheetNum == 0) {
				sheetNum = 1;
			}
			if (StringUtils.isBlank(fileName)) {
				fileName = String.valueOf(System.currentTimeMillis())+EXPAND_NAME;
			}
			String fileTempName = URLEncoder.encode(fileName, "UTF-8"); //防止中文文件名乱码
			int tempRowCount = 0; //行号指示器
			int headerNum = 0; //表头行数
			//遍历记录到excel
			for (int m = 0; m < sheetNum; m++) {
				if (headers == null || headers.size() == 0 ) {
					wsheet = ExcelCommon.excelOut("sheet" + m, m, titleList); //表格标题
				}else {
					headerNum = headers.size();
					wsheet = ExcelCommon.excelOut("sheet" + m, m,headerNum,titleList); //表格标题
					for (int h = 0; h < headerNum; h++) {  //表头部分
						wsheet.mergeCells(0, h, cellCount-1, h); 
						wsheet.addCell(new Label(0, h,headers.get(h)));
					}
				}
				tempRowCount = headerNum;
				//数据写入表格
				for (int i = m * sheetSize; i < (m + 1) * sheetSize && i < length; i++) {
					tempRowCount ++;
					List<String> tmpList = contentList.get(i);
					for (int j = 0; j<cellCount; j++) {
						wsheet.addCell(new Label(j, tempRowCount, tmpList.get(j)));
					}
				}
				//表格尾部
				if (m == sheetNum-1) { //最后一页
					if (footers != null && footers.size() > 0) {
						for(int k = 0;k<footers.size();k++){
							wsheet.mergeCells(0, tempRowCount+1+k, cellCount-1, tempRowCount+1+k); 
							wsheet.addCell(new Label(0, tempRowCount+1+k, footers.get(k)));
						}
					}
				}
			}
			response.setHeader("Content-disposition", "attachment; filename="+fileTempName+"");
			response.setContentType("application/msexcel;charset=UTF-8");
			//excel 写回到客户端
			ExcelCommon.getWritableWorkbook().write();
			ExcelCommon.getWritableWorkbook().close();
		} catch (Exception e) {
			log.error("ExcelExportUtils----exportExcel", e);
			throw e;
		}
	}
	
	/**
	 * 生成excel文件，保存在硬盘上
	 * @param titleList 表格标题(不为空)
	 * @param headers  表格头，在标题之上
	 * @param footers  表格尾部内容（常用于统计信息）
	 * @param contentList 表格内容
	 * @param sheetCount  每页数据最大条数(默认为50000)
	 * @param dirPath    文件目录
	 * @param fileName    excel文件名(带有扩展名)
	 */
	public static void createExcelFile(List<String> titleList,List<String> headers,List<String> footers,List<List<String>> contentList,
			Integer sheetCount,String dirPath,String fileName) throws Exception{
		if (titleList == null || titleList.size() == 0 || contentList == null || StringUtils.isBlank(dirPath)) {
			log.info("createExcelFile process exit");
			return;
		}
		if (StringUtils.isBlank(fileName)) {
			fileName = String.valueOf(System.currentTimeMillis())+EXPAND_NAME;
		}
		FileOutputStream fos = null;
		try {
			File folder = new File(dirPath);
			if (!folder.exists()) {
				folder.mkdirs();
			}
			File file = new File(dirPath + File.separator + fileName);
			if (file.exists()) {
				file.delete();
			}
			fos = new FileOutputStream(file);
			WritableSheet wsheet = null;
			ExcelCommon.getWritableWorkbook(fos);
			int cellCount = titleList.size();   //单元格数
			int length = contentList.size();      //数据条数
			int sheetSize = (sheetCount == null ? EXCEL_SHEETSIZE : sheetCount.intValue());
			if (sheetSize == 0) {
				sheetSize = EXCEL_SHEETSIZE;
			}
			int sheetNum = 0;  //sheet页数
			if (length % sheetSize > 0) {
				sheetNum = length / sheetSize + 1;
			} else {
				sheetNum = length / sheetSize;
			}
			if (sheetNum == 0) {
				sheetNum = 1;
			}
			int tempRowCount = 0; //行号指示器
			int headerNum = 0; //表头行数
			//遍历记录到excel
			for (int m = 0; m < sheetNum; m++) {
				if (headers == null || headers.size() == 0 ) {
					wsheet = ExcelCommon.excelOut("sheet" + m, m, titleList); //表格标题
				}else {
					headerNum = headers.size();
					wsheet = ExcelCommon.excelOut("sheet" + m, m,headerNum,titleList); //表格标题
					for (int h = 0; h < headerNum; h++) { //表头部分
						wsheet.mergeCells(0, h, cellCount-1, h); 
						wsheet.addCell(new Label(0, h,headers.get(h)));
					}
				}
				tempRowCount = headerNum;
				//数据写入表格
				for (int i = m * sheetSize; i < (m + 1) * sheetSize && i < length; i++) {
					tempRowCount ++;
					List<String> tmpList = contentList.get(i);
					for (int j = 0; j<cellCount; j++) {
						wsheet.addCell(new Label(j, tempRowCount, tmpList.get(j)));
					}
				}
				//表格尾部
				if (m == sheetNum-1) { //最后一页
					if (footers != null && footers.size() > 0) {
						for(int k = 0;k<footers.size();k++){
							wsheet.mergeCells(0, tempRowCount+1+k, cellCount-1, tempRowCount+1+k); 
							wsheet.addCell(new Label(0, tempRowCount+1+k, footers.get(k)));
						}
					}
				}
			}
			ExcelCommon.getWritableWorkbook().write();
			ExcelCommon.getWritableWorkbook().close();
		} catch (Exception e) {
			log.error("ExcelExportUtils----createExcelFile", e);
			throw e;
		} finally{
			try {
				if (fos != null) {
					fos.close();
				}
				log.info("createExcelFile finished!");
			} catch (Exception ex) {
				log.error("FileOutputStream关闭异常", ex);
			}
		}
	}
}
